package org.example;

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class ExcelProcessorThree_old {

    public static void main(String[] args) {
        String in = "C:\\Users\\luke\\Desktop\\2.5.xlsx";
        String out = "C:\\Users\\luke\\Desktop\\2.7\\";
        String sheetName = "Sheet1";
        System.out.println("->开始处理！" + sheetName);

        try {
            FileInputStream fis = new FileInputStream(in);
            Workbook workbook = WorkbookFactory.create(fis);
            Sheet sheet = workbook.getSheet(sheetName);

            if (sheet != null) {
                int rowCount = sheet.getLastRowNum();
                DataFormatter dataFormatter = new DataFormatter();
                System.out.print("->使用HashMap来存储不同D列值对应的工作表");
                // 使用HashMap来存储不同D列值对应的工作表
                HashMap<String, Workbook> workbookMap = new HashMap<>();
                List<Row> rowsToCopy = new ArrayList<>();

                for (int i = 1; i <= rowCount; i++) {
                    Row currentRow = sheet.getRow(i);
                    Cell currentCell = currentRow.getCell(12); // 获取8列的单元格
                    System.out.println("->获取" + i + "列的单元格");
                    String cellValue = dataFormatter.formatCellValue(currentCell);

                    if (!workbookMap.containsKey(cellValue)) {
                        // 如果该D列值还没有对应的工作表，则创建一个新的工作表
                        System.out.println("->如果该D列值还没有对应的工作表，则创建一个新的工作表");
                        Workbook newWorkbook = new XSSFWorkbook();
                        newWorkbook.createSheet("Sheet" + (i - 1));
                        workbookMap.put(cellValue, newWorkbook);
                    }

                    // Add rows with non-empty cells in D column to the list
                    if (!isRowEmpty(currentRow)) {
                        rowsToCopy.add(currentRow);
                    }
                }

                // Copy rows to corresponding workbooks
                for (Row currentRow : rowsToCopy) {
                    Cell currentCell = currentRow.getCell(12); // 获取8列的单元格
                    String cellValue = dataFormatter.formatCellValue(currentCell);
                    Workbook currentWorkbook = workbookMap.get(cellValue);
                    System.out.println("->获取对应D列值的工作表，并在工作表中添加当前行的数据:" + cellValue);
                    Sheet currentSheet = currentWorkbook.getSheetAt(0);
                    int newRowIdx = currentSheet.getLastRowNum() + 1;
                    Row newRow = currentSheet.createRow(newRowIdx);

                    for (int j = 0; j < currentRow.getLastCellNum(); j++) {
                        Cell currentSourceCell = currentRow.getCell(j);
                        Cell newCell = newRow.createCell(j);

                        // 复制单元格的值
                        newCell.setCellValue(dataFormatter.formatCellValue(currentSourceCell));
                        System.out.println("->复制单元格的值:" + currentSourceCell);
                        // 复制单元格的样式
                        CellStyle newCellStyle = currentWorkbook.createCellStyle();
                        newCellStyle.cloneStyleFrom(currentSourceCell.getCellStyle());
                        newCell.setCellStyle(newCellStyle);
                    }
                }



                // 将不同D列值对应的工作表写入不同的文件
                for (String cellValue : workbookMap.keySet()) {
                    String outputFile = out + cellValue + ".xlsx";
                    System.out.println("将不同D列值对应的工作表写入不同的文件:" + outputFile);
                    FileOutputStream fos = new FileOutputStream(outputFile);
                    Workbook currentWorkbook = workbookMap.get(cellValue);
                    currentWorkbook.write(fos);
                    fos.close();
                    currentWorkbook.close();
                }

                System.out.println("->处理完成！");
            } else {
                System.out.println("->指定的表格不存在！");
            }

            workbook.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // Helper method to check if a row is empty
    private static boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
        int o=0;
        System.out.println("->删除空行->"+o);
        return true;
    }
}
